//
//  SqliteDataHandle.m
//  LessonUI19Day - SQL
//
//  Created by lanou3g on 15/7/3.
//  Copyright (c) 2015年 lanou3g. All rights reserved.
//

#import "SqliteDataHandle.h"
//在.h里面用class 在,m里面用#import

@implementation SqliteDataHandle
static SqliteDataHandle *sQH = nil;
+(SqliteDataHandle *)shareSqliteDataHandle{
    //加上线程锁,保证访问安全
    @synchronized(self){
    if (nil == sQH) {
        sQH = [[SqliteDataHandle alloc] init];
    }
    return sQH;
    }
}

//声明一个数据库指针,用它来操作我们的数据库
static sqlite3 *sq = nil;

-(void)openSqlite{
    //1.找到sqlite的路径
    NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *sqlitePath = [documentPath stringByAppendingPathComponent:@"Product.sqlite"];
    NSLog(@"%@",sqlitePath);
    //2.创建数据表
    //打开数据库
    int openResult = sqlite3_open([sqlitePath UTF8String], &sq);
    if (openResult != SQLITE_OK) {
        NSLog(@"打开数据库失败!!!");
        return;
    }
    //建表
    NSString *creatSQL = @"create table product(product_type text primary key not NULL, product_data BLOB not NULL)";
    //运行sql语句
    char *error = nil;
    int result = sqlite3_exec(sq, [creatSQL UTF8String], NULL, NULL, &error);
    //3.判断表是否创建成功
    if (result == SQLITE_OK) {
        NSLog(@"数据表创建成功!!!");
    }else{
        NSLog(@"数据表创建失败:%s",error);
    }
}

#pragma mark - 关闭数据库
-(void)closeSqlite{
    int result = sqlite3_close(sq);
    if (result == SQLITE_OK) {
        sq = nil;
    }
}

#pragma mark - 插入用户信息
-(void)insertProductWithData:(NSData *)data type:(NSString *)type{
    //1.插入数据的sql语句  insert into 表名 (字段1,字段2,...) values ('值1','值2'...)
    NSString *insertSQL = [NSString stringWithFormat:@"insert into product (product_type,product_data) values ('%@','%@')",type,data];
    //2.执行sqlyuju
    char *error = nil;
    int result = sqlite3_exec(sq, [insertSQL UTF8String], NULL, NULL, &error);
    //3.判断
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败: %s",error);
    }
    
}
#pragma  mark - 查询所有用户
-(NSArray *)selectAllProduct{
    //1.查询的sql语句 当我们按照条件查询表中的数据时,sql的格式是select *from 表名 where 字段1='值1' and 字段2='值2';
    NSString *selectSQL = @"select *from product";
    //2.创建一个跟随指针
    sqlite3_stmt *stm = nil;
    //3.执行sql语句
    int result = sqlite3_prepare_v2(sq, [selectSQL UTF8String], -1, &stm, nil);
    
    NSMutableArray *productArray = [NSMutableArray array];
    //4.如果执行成功就去取值
    if (result == SQLITE_OK) {
        //通过循环去一个一个的去取值
        
        while (sqlite3_step(stm) == SQLITE_ROW) {
            //取值 当我们没有自增id,数据表会给我们生成一个rowid字段,但是这个字段没有计算在表的字段内
            char *type = (char *)sqlite3_column_text(stm, 0);
            NSData *data = (NSData *)sqlite3_column_text(stm, 1);
            [productArray addObject:[NSString stringWithUTF8String:type]];
            [productArray addObject:data];
        }
    }else{
        NSLog(@"执行错误!!!");
    }
    //释放跟随指针
    sqlite3_finalize(stm);
    return productArray;
}

#pragma mark - 删除
-(void)deleteProductWithType:(NSString *)type{
    //删除的sql语句 ; 格式:delete from 表名 where 字段='值';
    NSString *deleteSQL = [NSString stringWithFormat:@"delete from user where user_type = '%@'",type];
    //执行sql语句
    char *error = nil;
    int result = sqlite3_exec(sq, [deleteSQL UTF8String], nil, nil, &error);
    //判断执行结果
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败: %s",error);
    }
}

#pragma mark - 修改用户信息
-(void)updateProductWithTpye:(NSString *)type{
    //修改的sql语句 格式为: update 表名 set 字段1 = '值1',字段2 = '值2' where  字段 = '值';
    NSString *updateSQl = [NSString stringWithFormat:@"update product set product_type = 'xiaoHuoLong' where product_type = '%@'",type];
    //执行sql语句
    char *error = nil;
    int result = sqlite3_exec(sq, [updateSQl UTF8String], nil, nil, &error);
    //判断执行结果
    if (result == SQLITE_OK) {
        NSLog(@"修改成功");
    }else{
        NSLog(@"修改失败: %s",error);
    }
}

#pragma mark - 按照名字搜索用户
-(NSArray *)selectProductDataWithType:(NSString *)type{
    NSString *seclectSQL = [NSString stringWithFormat:@"select *from product where product_type = '%@'",type];
    sqlite3_stmt *stm = nil;
    int result = sqlite3_prepare_v2(sq, [seclectSQL UTF8String], -1, &stm, nil);
    
    NSMutableArray *ProductArray = [NSMutableArray array];
    if (result == SQLITE_OK) {
        //通过循环去一个一个的去取值
        NSLog(@"查询成功");
        while (sqlite3_step(stm) == SQLITE_ROW) {
            //取值 当我们没有自增id,数据表会给我们生成一个rowid字段,但是这个字段没有计算在表的字段内
            char *type = (char *)sqlite3_column_text(stm, 0);
            NSData *data = (NSData *)sqlite3_column_text(stm, 1);
            [ProductArray addObject:[NSString stringWithUTF8String:type]];
            [ProductArray addObject:data];
        }
    }else{
        NSLog(@"执行错误!!!");
    }
    //释放跟随指针
    sqlite3_finalize(stm);
    
    return ProductArray;
}




@end
